VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} frmContracts 
   Caption         =   "Current and New Contract Statuses"
   ClientHeight    =   18000
   ClientLeft      =   0
   ClientTop       =   -33000
   ClientWidth     =   18000
   OleObjectBlob   =   "frmContracts.frx":0000
   StartUpPosition =   1  'CenterOwner
End
Attribute VB_Name = "frmContracts"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Written by Jalisa Greene
'Last Modified by Jalisa Greene
'Last Updated 6/11/2014

Option Explicit  '**all variables need to be declared**'

Private Sub UserForm_Initialize()
'**ACTION SUB CALLED WHEN USER OPEN THE FORM**'

'**ADD ALL AVAILABLE CONTRACTS TO THE LIST**'
    lstContractList.Clear
    Dim aCell As Range
    
    '**CURRENT CONTRACTS FIRST, THEN NEW CONTRACTS**'
    lstContractList.AddItem (" - Old Contracts -")
    For Each aCell In Worksheets("Contract Data").Range("A10:A1100")
        If Worksheets("Contract Data").Range("E" & aCell.Row).Value = "Current Contract" Then lstContractList.AddItem (Worksheets("Contract Data").Range("C" & aCell.Row).Value)
    Next
    lstContractList.AddItem (" - New Contracts -")
    For Each aCell In Worksheets("Contract Data").Range("A10:A1100")
        If Worksheets("Contract Data").Range("E" & aCell.Row).Value <> "Current Contract" And Worksheets("Contract Data").Range("E" & aCell.Row).Value <> "" Then lstContractList.AddItem (Worksheets("Contract Data").Range("C" & aCell.Row).Value)
    Next
    
    '**ADD CONTRACT STATUS DROPDOWN SELECTIONS**'
    For Each aCell In Worksheets("Pick Lists").Range("L3:L7")
        cmbStatus.AddItem (aCell.Value)
    Next
    '**ADD CONTRACT TYPE DROPDOWN SELECTIONS**'
    For Each aCell In Worksheets("Pick Lists").Range("M3:M5")
        cmbContractType.AddItem (aCell.Value)
    Next
    '**ADD OLD CONTRACTS TO THE DROPDOWN FOR ASSOCIATION TO NEW CONTRACTS**'
    For Each aCell In Worksheets("Contract Data").Range("C10:C1100")
        If aCell.Value <> "" Then cmbOldContract.AddItem (aCell.Value)
    Next
                                
End Sub

Private Sub btnView_Click()
'**ACTION SUB WHEN A USER SELECTS A CONTRACT AND HITS VIEW**'

   multipgContract.Value = 1
    Call InsertCurrContractData(lstContractList.Value)
End Sub

Private Sub InsertCurrContractData(contractid As String)
'**CALLED AFTER THE USER SELECTS TO VIEW THE CONTRACT**'

    Dim rowNum As Integer
    Dim aCell As Range
    Dim msg As Integer
    If contractid <> " - New Contracts -" And contractid <> " - Old Contracts -" Then
        

        '**ERROR CHECKER**'
        On Error GoTo ErrorResume:
        
        '**ATTEMPT TO MATCH SELECTED CONTRACT, THEN PULL ASSOCIATED INFORMATION**'
        rowNum = WorksheetFunction.Match(contractid, Worksheets("Contract Data").Range("C10: C1100"), 0) + 9
    
        txtVendor.Text = Worksheets("Contract Data").Range("D" & rowNum).Value
        txtRepName.Text = Worksheets("Contract Data").Range("V" & rowNum).Value
        txtRepNum.Text = Worksheets("Contract Data").Range("W" & rowNum).Value
        txtRepEmail.Text = Worksheets("Contract Data").Range("X" & rowNum).Value
        txtClientSig.Text = Worksheets("Contract Data").Range("Y" & rowNum).Value
        txtDateofAgr.Text = Format(Worksheets("Contract Data").Range("F" & rowNum).Value, "mm/dd/yyyy")
        txtLengthInitTerm.Value = Worksheets("Contract Data").Range("G" & rowNum).Value
        txtInitTermExpiration.Text = Format(Worksheets("Contract Data").Range("H" & rowNum).Value, "mm/dd/yyyy")
        
        txtAutorenewalLength.Value = Worksheets("Contract Data").Range("I" & rowNum).Value
        txtExpirationDate.Text = Format(Worksheets("Contract Data").Range("J" & rowNum).Value, "mm/dd/yyyy")

        If Worksheets("Contract Data").Range("AA" & rowNum).Value = "Yes" Then
            chkPenalty.Value = True
        Else: chkPenalty.Value = False
        End If
        If Worksheets("Contract Data").Range("AB" & rowNum).Value = "Yes" Then
                chkDupInvoice.Value = True
        Else: chkDupInvoice.Value = False
        End If
        txtPriceIncr.Text = Worksheets("Contract Data").Range("O" & rowNum).Value
        txtETFs.Value = Worksheets("Contract Data").Range("P" & rowNum).Value
        txtFuelEnv.Value = Worksheets("Contract Data").Range("N" & rowNum).Value
        txtAvgPercIncr.Value = Worksheets("Contract Data").Range("M" & rowNum).Value
        txtNotes.Text = Worksheets("Contract Data").Range("AC" & rowNum).Value
        cmbStatus.Value = Worksheets("Contract Data").Range("Z" & rowNum).Value
        txtUniqueID.Value = Worksheets("Contract Data").Range("AE" & rowNum).Value
        txtSource.Value = Worksheets("Contract Data").Range("E" & rowNum).Value
        cmbContractType.Value = Worksheets("Contract Data").Range("Q" & rowNum).Value
        
        cmbOldContract.Value = Worksheets("Contract Data").Range("AF" & rowNum).Value
        txtNumLoc.Value = Worksheets("Contract Data").Range("AG" & rowNum).Value
        txtCSPhone.Value = Worksheets("Contract Data").Range("AH" & rowNum).Value

    End If

    '**INSERT THE LIST OF CONTAINERS THIS CONTRACT IS ASSOCIATED WITH**'
    Call InsertContainers
    Exit Sub

ErrorResume:
    MsgBox ("Error! uh oh...., call Jalisa :)")
    Exit Sub
    
End Sub
Private Sub InsertContainers()
'**USED TO FIND WHICH CONTAINERS HAVE THIS CONTRACT ASSOCIATED TO THEM AND LIST THEM ON THE CONTRACT'S PAGE**'

    Dim aCell As Range
    Dim vaItems As Variant
    Dim I As Long, j As Long
    Dim vTemp As Variant
    

    '**GOES THORUGH EACH CONTRACT ID COLUMN FOR ALL CONTAINERS TO SEE IF CONTRACT IDS MATCH**'
    lstContainers.Clear
    For Each aCell In Worksheets("ASR").Range("BD8:BD1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("DD8:DD1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("FD8:FD1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("HD8:HD1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("JD8:JD1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("LJ8:LJ1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("NJ8:NJ1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("PJ8:PJ1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AGA8:AGA1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AHH8:AHH1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AIO8:AIO1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AJV8:AJV1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("ALC8:ALC1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AMJ8:AMJ1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("ANV8:ANV1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("APH8:APH1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
      For Each aCell In Worksheets("ASR").Range("AQT8:AQT1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("ASA8:ASA1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("ATH8:ATH1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AUO8:AUO1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AVV8:AVV1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AXC8:AXC1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("AYO8:AYO1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    For Each aCell In Worksheets("ASR").Range("BAA8:BAA1099")
        If aCell.Offset(0, 1).Value = lstContractList.Value Then lstContainers.AddItem (aCell.Value)
    Next
    
    '**SORT RECIEVED CODE FROM JOHN WALKENBACH EXCEL POWER PROGRAMMING**'
    '**WITH VBA TO SORT ARRAY
    '**PUT THE ITEMS IN A VARIANT ARRAY**'
    vaItems = lstContainers.List
   
    For I = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
    For j = I + 1 To UBound(vaItems, 1)
         If vaItems(I, 0) > vaItems(j, 0) Then
                vTemp = vaItems(I, 0)
                vaItems(I, 0) = vaItems(j, 0)
                vaItems(j, 0) = vTemp
            End If
        Next j
    Next I
    
    '**CLEAR THE LISTBOX AND REPOPULATE THE SORTED LIST BACK IN THE BOX**
    lstContainers.Clear
    For I = LBound(vaItems, 1) To UBound(vaItems, 1)
        lstContainers.AddItem vaItems(I, 0)
    Next I
End Sub


Private Sub btnSave1_Click()
'**ACTION SUB, RUNS WHEN USER CLICKS SAVE**'

    Dim rowNum As Integer
    Dim aCell As Range
    Dim msg As Integer
    
    If txtSource.Value <> "" And txtVendor.Value <> "" Then
        '**FIND THE ROW NUMBER OF THE SELECTED CONTRACT ID**'
        rowNum = WorksheetFunction.Match(lstContractList.Value, Worksheets("Contract Data").Range("C10:C1100"), 0) + 9

        Worksheets("Contract Data").Range("D" & rowNum).Value = txtVendor.Text
        Worksheets("Contract Data").Range("V" & rowNum).Value = txtRepName.Value
        Worksheets("Contract Data").Range("W" & rowNum).Value = txtRepNum.Value
        Worksheets("Contract Data").Range("X" & rowNum).Value = txtRepEmail.Value
        Worksheets("Contract Data").Range("Y" & rowNum).Value = txtClientSig.Value
        Worksheets("Contract Data").Range("F" & rowNum).Value = txtDateofAgr.Value
        Worksheets("Contract Data").Range("G" & rowNum).Value = txtLengthInitTerm.Value
        Worksheets("Contract Data").Range("H" & rowNum).Value = txtInitTermExpiration.Value
        Worksheets("Contract Data").Range("E" & rowNum).Value = txtSource.Value
    
        Worksheets("Contract Data").Range("I" & rowNum).Value = txtAutorenewalLength.Value
        Worksheets("Contract Data").Range("J" & rowNum).Value = txtExpirationDate.Value
        

        Worksheets("Contract Data").Range("O" & rowNum).Value = txtPriceIncr.Value
        Worksheets("Contract Data").Range("P" & rowNum).Value = txtETFs.Value
        Worksheets("Contract Data").Range("N" & rowNum).Value = txtFuelEnv.Value
        Worksheets("Contract Data").Range("M" & rowNum).Value = txtAvgPercIncr.Value
        Worksheets("Contract Data").Range("AC" & rowNum).Value = txtNotes.Value
        Worksheets("Contract Data").Range("Z" & rowNum).Value = cmbStatus.Value
        Worksheets("Contract Data").Range("AE" & rowNum).Value = txtUniqueID.Value
        Worksheets("Contract Data").Range("Q" & rowNum).Value = cmbContractType.Value
        Worksheets("Contract Data").Range("AF" & rowNum).Value = cmbOldContract.Value
        Worksheets("Contract Data").Range("AG" & rowNum).Value = txtNumLoc.Value
        Worksheets("Contract Data").Range("AH" & rowNum).Value = txtCSPhone.Value

        '**IF DUPLICATE INVOICE BOX CHEKCKED, THEN PUT YES IN THAT COLUMN**'
        If Worksheets("Contract Data").Range("AB" & rowNum).Value <> "" Then
            If chkDupInvoice.Value = True Then
                Worksheets("Contract Data").Range("AB" & rowNum).Value = "Yes"
            Else:
                Worksheets("Contract Data").Range("AB" & rowNum).Value = "No"
            End If
        End If
        
        '**IF PENALTY BOX CHECKED, THEN PUT A YES IN THAT COLUMN, ELSE PUT A NO**'
        If Worksheets("Contract Data").Range("AA" & rowNum).Value <> "" Then
            If chkPenalty.Value = True Then
                Worksheets("Contract Data").Range("AA" & rowNum).Value = "Yes"
            Else:
                Worksheets("Contract Data").Range("AA" & rowNum).Value = "No"
            End If
        End If
        
    End If
        
    'GO BACK TO FIRST PAGE, CLEAR OUT THE LIST OF CONTRACTS, PUT UPDATED INFORMATION IN THERE**'
    multipgContract.Value = 0
    frmContracts.lstContractList.Clear
    lstContractList.AddItem (" - Old Contracts -")
    For Each aCell In Worksheets("Contract Data").Range("A10:A1100")
        If Worksheets("Contract Data").Range("E" & aCell.Row).Value = "Current Contract" Then lstContractList.AddItem (Worksheets("Contract Data").Range("C" & aCell.Row).Value)
    Next
    lstContractList.AddItem (" - New Contracts -")
    For Each aCell In Worksheets("Contract Data").Range("A10:A1100")
        If Worksheets("Contract Data").Range("E" & aCell.Row).Value <> "Current Contract" And Worksheets("Contract Data").Range("E" & aCell.Row).Value <> "" Then lstContractList.AddItem (Worksheets("Contract Data").Range("C" & aCell.Row).Value)
    Next
End Sub

Private Sub btnAddContract_Click()
'**ACTION SUB THAT CALLS NEWCONTRACT USERFORM IF USER WANTS TO ADD NEW**'

    frmNewContract.Show
End Sub
